package common.util.db;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Random;
import java.util.Vector;

import common.util.date.DateUtil;
import common.util.json.JsonUtil;
import common.util.string.StringUtil;


/**
 * 数据库操作类
 * 
 * @author Eric.tyc
 * @version 1.0.1 | 2012-06-20
 */
public class DbUtil {

	/**
	 * 数据库实例
	 */
	private static HashMap<String, DbUtil> instance = new HashMap<String, DbUtil>();
	
	/**
	 * 日志实例
	 */
	
	/**
	 * 连接池实例
	 */
	ConnectionPool connPool = null;
	
	String serverIp;
	String dbName;
	String userName;
	String password;
	
	/**
	 * 构造函数
	 */
	private DbUtil(String serverIp, String dbName, String userName, String password) {
		this.serverIp=serverIp;
		this.dbName=dbName;
		this.userName=userName;
		this.password=password;
		connPool = new ConnectionPool(serverIp, dbName, userName, password);
	}
	
	/**
	 * 获取数据库实例
	 * 
	 * @param sfsExt
	 * @return instance
	 */
	public static DbUtil getInstance(String serverIp, String dbName, String userName, String password) {
		serverIp = serverIp.trim();
		dbName = dbName.trim();
		userName = userName.trim();
		password = password.trim();
		
		if ( (serverIp == null || serverIp.isEmpty()) || 
				(dbName == null || dbName.isEmpty()) ||
				(userName == null || userName.isEmpty()) ) {
			throw new RuntimeException("please checkout connect db param~");
		}
		
		String insKey = serverIp + ":" + dbName;
		DbUtil insData = null;
		
		if ( !instance.containsKey(insKey) ) {
			insData = new DbUtil(serverIp, dbName, userName, password);
			instance.put(insKey, insData);
		} else {
			insData = instance.get(insKey);
		}
		
		return insData;
	}
	
	public static DbUtil getInstance() {
		return getInstance(SysConstant.GAME_DATABASE_SERVER_IP, SysConstant.GAME_DATABASE_NAME, 
				SysConstant.GAME_DATABASE_USERNAME, SysConstant.GAME_DATABASE_PASSWORD);
	}
	
	/**
	 * 获取数据库连接
	 * 
	 * @param sfsExt
	 * @return
	 */
	public Connection getConnection() {
		Connection conn = connPool.getConnection();
		boolean testConn = checkConnection(conn);
		//可不使用,测试的时候会重连
//		if(!testConn){
//			System.out.println("::::::reconnect!!!");
//			connPool = new ConnectionPool(serverIp, dbName, userName, password);
//			conn=connPool.getConnection();
//		}
		return conn;
	}
	
	
	/*******************************************************************
	 ** 以下为静态操作方法 
	 *******************************************************************/
	
	
	/**
	 * 获取单行数据{使用默认dbInstance}
	 * (取出数据表所有字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			LinkedHashMap<String, Object> where) {
		return getRow(tableName, makeWhere(where), "*", getInstance());
	}
	
	/**
	 * 获取单行数据
	 * (取出数据表所有字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			LinkedHashMap<String, Object> where, DbUtil dbInstance) {
		return getRow(tableName, makeWhere(where), "*", dbInstance);
	}

	/**
	 * 获取单行数据{使用默认dbInstance}
	 * (取出数据表所有字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName,  
			String where) {
		return getRow(tableName, where, "*", getInstance());
	}
	
	/**
	 * 获取单行数据
	 * (取出数据表所有字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName,  
			String where, DbUtil dbInstance) {
		return getRow(tableName, where, "*", dbInstance);
	}
	
	/**
	 * 获取单行数据{使用默认dbInstance}
	 * (需要指定数据表字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			LinkedHashMap<String, Object> where, String cols) {
		return getRow(tableName, makeWhere(where), cols, getInstance());
	}
	
	/**
	 * 获取单行数据
	 * (需要指定数据表字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			LinkedHashMap<String, Object> where, String cols, DbUtil dbInstance) {
		return getRow(tableName, makeWhere(where), cols, dbInstance);
	}
	
	/**
	 * 获取单行数据{使用默认dbInstance}
	 * (需要指定数据表字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			String where, String cols) {
		return getRow(tableName, where, cols, getInstance());
	}
	
	/**
	 * 获取单行数据
	 * (需要指定数据表字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> getRow(String tableName, 
			String where, String cols, DbUtil dbInstance) {
		
		if ( cols == null || cols.isEmpty() ) {
			cols = "*";
		} else {
			cols = cols.trim();
		}
		String sql = "SELECT " + cols + " FROM `" + tableName.trim() + "` " + makeWhere(where) + " LIMIT 1";
		return dbInstance.query(sql);
	}
	
	/**
	 * 获取多行数据{使用默认dbInstance}
	 * (取出数据表所有字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			LinkedHashMap<String, Object> where) {
		return select(tableName, makeWhere(where), "*", getInstance());
	}
	
	/**
	 * 获取多行数据
	 * (取出数据表所有字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			LinkedHashMap<String, Object> where, DbUtil dbInstance) {
		return select(tableName, makeWhere(where), "*", dbInstance);
	}
	
	/**
	 * 获取多行数据{使用默认dbInstance}
	 * (取出数据表所有字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			String where) {
		return select(tableName, where, "*", getInstance());
	}
	
	/**
	 * 获取多行数据
	 * (取出数据表所有字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			String where, DbUtil dbInstance) {
		return select(tableName, where, "*", dbInstance);
	}
	
	/**
	 * 获取多行数据{使用默认dbInstance}
	 * (需指定数据表字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @param cols
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			LinkedHashMap<String, Object> where, String cols) {
		return select(tableName, makeWhere(where), cols, getInstance());
	}
	
	/**
	 * 获取多行数据
	 * (需指定数据表字段)
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param where
	 * @param cols
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			LinkedHashMap<String, Object> where, String cols, DbUtil dbInstance) {
		return select(tableName, makeWhere(where), cols, dbInstance);
	}
	
	/**
	 * 获取多行数据{使用默认dbInstance}
	 * (需指定数据表字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @param cols
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			String where, String cols) {
		return select(tableName, where, cols, getInstance());
	}
	
	/**
	 * 获取多行数据
	 * (需指定数据表字段)
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param where
	 * @param cols
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> select(String tableName, 
			String where, String cols, DbUtil dbInstance) {
		
		if ( cols == null || cols.isEmpty() ) {
			cols = "*";
		} else {
			cols = cols.trim();
		}
		
		String sql = "SELECT " + cols + " FROM `" + tableName.trim() + "` " + makeWhere(where);
		return dbInstance.query(sql);
	}
	
	/**
	 * 根据SQL获取数据{使用默认dbInstance}
	 * 
	 * @param sql
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> sql(String sql) {
		return sql(sql, getInstance());
	}
	
	/**
	 * 根据SQL获取数据
	 * 
	 * @param sql
	 * @param dbInstance
	 * @return
	 */
	public static LinkedList<HashMap<String, Object>> sql(String sql, DbUtil dbInstance) {
		return dbInstance.query(sql);
	}
	
	/**
	 * 获取结果记录数{使用默认dbInstance}
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static int count(String tableName, LinkedHashMap<String, Object> where) {
		return count(tableName, makeWhere(where), getInstance());
	}
	
	/**
	 * 获取结果记录数
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int count(String tableName, LinkedHashMap<String, Object> where, DbUtil dbInstance) {
		return count(tableName, makeWhere(where), dbInstance);
	}
	
	/**
	 * 获取结果记录数{使用默认dbInstance}
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static int count(String tableName, String where) {
		return count(tableName, where, getInstance());
	}
	
	/**
	 * 获取结果记录数
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int count(String tableName, String where, DbUtil dbInstance) {
		String sql = "SELECT COUNT(1) AS count FROM `" + tableName + "` " + makeWhere(where);
		LinkedList<HashMap<String, Object>> result = dbInstance.query(sql);
		
		int count = 0;
		if ( result != null && !result.isEmpty() ) {
			count = Integer.parseInt(result.getFirst().get("count").toString());
		}
		
		return count;
	}
	
	/**
	 * 往数据库插入数据{使用默认dbInstance}
	 * 
	 * @param tableName
	 * @param data
	 * @return
	 */
	public static int insert(String tableName, LinkedHashMap<String, Object> data) {
		return insert(tableName, data, getInstance());
	}
	
	/**
	 * 往数据库插入数据
	 * 
	 * @param tableName
	 * @param data
	 * @param dbInstance
	 * @return
	 */
	public static int insert(String tableName, LinkedHashMap<String, Object> data, DbUtil dbInstance) {
		String sql = "INSERT INTO `" + tableName + "` " + makeInsertCols(data);
		return dbInstance.excute(sql);
	}
	
	/**
	 * 往数据库插入多行数据
	 * 
	 * @param tableName
	 * @param data
	 * @return
	 */
	public static int insertMulti(String tableName, LinkedList<LinkedHashMap<String, Object>> data) {
		return insertMulti(tableName, data, getInstance());
	}
	
	/**
	 * 往数据库插入多行数据
	 * 
	 * @param tableName
	 * @param data
	 * @param dbInstance
	 * @return
	 */
	public static int insertMulti(String tableName, LinkedList<LinkedHashMap<String, Object>> data, DbUtil dbInstance) {
		int effectRows = 0;
		
		//TODO
		//1. 使用事务，确保SQL所有都能插入成功
		//2. 
		
		return effectRows;
	}
	
	/**
	 * 更新数据{使用默认dbInstance}
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param data
	 * @param where
	 * @return
	 */
	public static int update(String tableName, LinkedHashMap<String, Object> data, 
			LinkedHashMap<String, Object> where) {
		return update(tableName, data, makeWhere(where), getInstance());
	}
	
	/**
	 * 更新数据
	 * (条件字段类型为LinkedHashMap)
	 * 
	 * @param tableName
	 * @param data
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int update(String tableName, LinkedHashMap<String, Object> data, 
			LinkedHashMap<String, Object> where, DbUtil dbInstance) {
		return update(tableName, data, makeWhere(where), dbInstance);
	}
	
	/**
	 * 更新数据{使用默认dbInstance}
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param data
	 * @param where
	 * @return
	 */
	public static int update(String tableName, LinkedHashMap<String, Object> data, 
			String where) {
		return update(tableName, data, where, getInstance());
	}
	
	/**
	 * 更新数据
	 * (条件字段类型为String)
	 * 
	 * @param tableName
	 * @param data
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int update(String tableName, LinkedHashMap<String, Object> data, 
			String where, DbUtil dbInstance) {
		String sql = "UPDATE `" + tableName + "` SET " + makeCols(data) + makeWhere(where);
		return dbInstance.excute(sql);
	}
	
	/**
	 * <b>删除数据</b></br></br>
	 * 本方法必须指定条件语句，</br>
	 * 为了防止where为空时把整个数据表记录清除</br>
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static int delete(String tableName, LinkedHashMap<String, Object> where) 
			throws SQLException {
		return delete(tableName, makeWhere(where), getInstance());
	}
	
	/**
	 * <b>删除数据</b></br></br>
	 * 本方法必须指定条件语句，</br>
	 * 为了防止where为空时把整个数据表记录清除</br>
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public static int delete(String tableName, LinkedHashMap<String, Object> where, 
			DbUtil dbInstance) throws SQLException {
		return delete(tableName, makeWhere(where), dbInstance);
	}
	
	/**
	 * <b>删除数据</b></br></br>
	 * 本方法必须指定条件语句，</br>
	 * 为了防止where为空时把整个数据表记录清除</br>
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int delete(String tableName, String where) 
			throws SQLException {
		return delete(tableName, where, getInstance());
	}
	
	/**
	 * <b>删除数据</b></br></br>
	 * 本方法必须指定条件语句，</br>
	 * 为了防止where为空时把整个数据表记录清除</br>
	 * 
	 * @param tableName
	 * @param where
	 * @param dbInstance
	 * @return
	 */
	public static int delete(String tableName, String where, DbUtil dbInstance) 
			throws SQLException {
		if ( StringUtil.isEmpty(where) ) {
			String errMsg = "delete condition{where} must be special!";
			throw new SQLException(errMsg);
		}
		String sql = "DELETE FROM `" + tableName + "` " + makeWhere(where);
		return dbInstance.excute(sql);
	}
	
	/**
	 * 执行指定SQL语句
	 * 
	 * @param sql
	 * @return
	 */
	public static boolean excuteSql(String sql) {
		int flag = getInstance().excute(sql);
		return flag > 0 ? true : false;
	}
	
	/**
	 * 执行指定SQL语句
	 * 
	 * @param sql
	 * @param dbInstance
	 * @return
	 */
	public static boolean excuteSql(String sql, DbUtil dbInstance) {
		int flag = dbInstance.excute(sql);
		return flag > 0 ? true : false;
	}
	
	/**
	 * 使用事务执行多条语句
	 * 
	 * @param sqls
	 * @return
	 */
	public static boolean executeUpdateSqls(String[] sqls){
		return getInstance().execute(sqls);
	}
	
	/**
	 * 使用事务执行多条语句
	 * 
	 * @param sqls
	 * @return
	 */
	public static boolean executeUpdateSqls(Vector<String> v){
		return getInstance().execute(v);
	}
	
	/*************************************************************
	 ** 以下为静态私有方法
	 *************************************************************/
	
	
	/**
	 * 生成用于insert的字串字段格式
	 * 类似于 (`UserName`, `Age`) VALUES ('abc', '20')
	 * 
	 * @param data
	 * @return
	 */
	public static String makeInsertCols(LinkedHashMap<String, Object> data) {
		StringBuilder cols = new StringBuilder();
		StringBuilder values = new StringBuilder();
		
		String connector = ", ";
		int connectorLen = connector.length();
		
		cols.append("(");
		values.append("(");
		
		for ( Map.Entry<String, Object> entry : data.entrySet() ) {
			String key = entry.getKey();
			Object value = entry.getValue();
			
			cols.append("`" + key + "`").append(connector);
			values.append("'" + value + "'").append(connector);
		}
		
		int colsLen = cols.length();
		int valuesLen = values.length();
		
		cols.delete(colsLen-connectorLen, colsLen).append(")");
		values.delete(valuesLen-connectorLen, valuesLen).append(")");
		
		return cols.toString() + " VALUES " + values.toString();
	}
	
	/**
	 * 生成SQL字段字串格式
	 * `UserName`='abc', `Age`='20'
	 * 
	 * @param data
	 * @return
	 */
	public static String makeCols(LinkedHashMap<String, Object> data) {
		return makeData(data, ", ");
	}
	
	/**
	 * 生成SQL条件字串格式
	 * 
	 * 使用LinkedHashMap的key-value对生成
	 * 类似于 `UserName`='abc' AND `Age`='20',
	 * 其中 'else:' 用于特殊情况下的条件，比如 "group by xxx"等
	 * 
	 * @param where
	 * @return
	 */
	public static String makeWhere(LinkedHashMap<String, Object> where) {
		String elseWhere = "";
		if ( where.containsKey("else:") ) {
			elseWhere = where.get("else:").toString();
			where.remove("else:");
		}
		
		String newWhere = makeData(where, " AND ");
		if ( !StringUtil.isEmpty(newWhere) ) {
			newWhere = "AND " + newWhere;
		}
		
		return newWhere + " " + elseWhere;
	}
	
	/**
	 * 返回标准格式WHERE条件
	 * 
	 * @param where
	 * @return
	 */
	public static String makeWhere(String where) {
		if ( StringUtil.isEmpty(where) ) {
			where = "";
		}
		
		where = where.trim();
		if ( !where.equals("") ) {
			String upperCaseWhere = where.toUpperCase();
			String whereOptStr = " WHERE ";
			if ( !upperCaseWhere.startsWith(whereOptStr) ) {
				where = " WHERE 1 " + where;
			}
		}
		
		return where;
	}
	
	/**
	 * 生成字符串
	 * 类似于 `UserName`='abc' AND `Age`='20'
	 * 		 `UserName`='abc', `Age`='20'
	 * 
	 * @param where
	 * @return
	 */
	private static String makeData(LinkedHashMap<String, Object> where, String connector) {
		StringBuilder result = new StringBuilder();
		int connectorLen = connector.length();
		
		String str = "";
		if ( !where.isEmpty() ) {
			for ( Map.Entry<String, Object> entry : where.entrySet() ) {
				String key = entry.getKey();
				Object value = entry.getValue();
				result.append("`" + key + "`=" + "'" + value.toString() + "'");
				result.append(connector);
			}
			
			int resultLen = result.length();
			str = result.delete(resultLen-connectorLen, resultLen).toString();
		}
		return str;
	}
	
	/**
	 * 用于查询给定SQL语句
	 * (主要用于SELECT,SHOW TABLES等语句执行)
	 * 
	 * @param sql
	 * @return
	 */
	public LinkedList<HashMap<String, Object>> query(String sql) {
		LinkedList<HashMap<String, Object>> result = new LinkedList<HashMap<String, Object>>();
		
		if ( !StringUtil.isEmpty(sql) ) {
			sql = sql.trim();
		}
		
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
			if ( rs != null ) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				while ( rs.next() ) {
					HashMap<String, Object> data = new HashMap<String, Object>();
					for ( int i = 1; i <= columnCount; i++ ) {
						data.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					result.add(data);
				}
			}
		} catch ( SQLException e ) {
			e.printStackTrace();
		} finally {
			connPool.closeAll(conn, psmt, rs);
		}
		
		return result;
	}
	
	/**
	 * 校验是否丢失连接
	 * @param conn
	 * @return
	 */
    private boolean checkConnection(Connection conn) {  
        try {  
            Statement pingStatement = null;  
            try {  
                pingStatement = (Statement) conn.createStatement();  
                pingStatement.executeQuery("SELECT 1").close();//测试是否超时的另一种做法  
            } finally {  
                if (pingStatement != null) {  
                    pingStatement.close();  
                }  
            }  
            return true; //自定义值  
        } catch (SQLException e) {  
        	System.out.println("::::::connection time out! reconnect");
            return false; //自定义值  
        }  
    }  
	
	/**
	 * 用于执行给定SQL语句
	 * (主要用于INSERT, UPDATE, DELETE等语句执行)
	 * 
	 * @param sql
	 * @return iRtn 若为INSERT的则返回插入记录Id，
	 * 				其他为影响记录行数
	 */
	public int excute(String sql) {
		int iRtn = 0;
		
		sql = sql.trim();
		
		Connection conn = null;
		PreparedStatement psmt = null;
		
		try {
			conn = getConnection();
			String upperSql = sql.toUpperCase();
			if ( upperSql.startsWith("INSERT") ) {
				psmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				psmt.executeUpdate();
				
				int autoIncKey = -1;
				ResultSet rs = psmt.getGeneratedKeys();
				if ( rs.next() ) {
					autoIncKey = rs.getInt(1);
				}
				//获得插入数据的自增id
				iRtn = autoIncKey;
			} else {
				psmt = conn.prepareStatement(sql.toString().trim());
				iRtn = psmt.executeUpdate();
			}
		} catch ( SQLException e ) {
			e.printStackTrace();
		} finally {
			connPool.closeAll(conn, psmt, null);
		}
		
		return iRtn;
	}
	
	/**
	 * 使用事务执行多条SQL语句
	 * 
	 * @param sqls
	 * @return
	 */
	public boolean execute(String[] sqls) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			stmt=conn.createStatement();
			for(int i=0;i<sqls.length;i++){
				String sql = sqls[i];
//				String upperSql = sql.toUpperCase();
				stmt.executeUpdate(sql);
			}
			conn.commit();
		} catch ( SQLException e ) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				if(stmt!=null){
					stmt.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 使用事务执行多条SQL语句
	 * 
	 * @param sqls
	 * @return
	 */
	public boolean execute(Vector<String> v) {
		Connection conn = null;
		Statement stmt = null;
		Iterator<String> it = v.iterator();
		int count = v.size();
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			stmt=conn.createStatement();
			for(int i=0;i<count;i++){
				String sql = it.next();
				stmt.executeUpdate(sql);
				it.remove();
			}
			conn.commit();
		} catch ( SQLException e ) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				if(stmt!=null){
					stmt.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return true;
	}
	
	public static void main(String[] args) throws Exception {
		//sendTotalUserEmail();
		generateOldUserCdKey();
		//generateNewServerEmail();
		//updateOldUserKey();
		//sendTotalUserEmail(40000);
	}
	
	
	public static void generateActivityKey(){
		String filePath = "D:\\MyData\\desk\\emails.txt";
		int rndCharLength = 4;
		String charList = "abcdefghijklmnopqrstuvwxyz0123456789";
		int size =  33499;
		int type = 17;
		StringBuilder sqls = new StringBuilder();
		for(int i =0;i<size;i++){
			int charLength = charList.length();
			Random random = new Random();
			
			while(true){
				StringBuilder strBuilder = new StringBuilder();
				while ( true ) {
					if ( strBuilder.length() >= rndCharLength ) {
						break;
					}
					int index = random.nextInt(charLength);
					strBuilder.append(charList.charAt(index));
				}
			
			
				String cdKey = strBuilder.toString();
				int nowTime = DateUtil.getNowTime();
				
				LinkedHashMap<String, Object> where = new LinkedHashMap<String, Object>();
				where.put("CdKey", cdKey);
				LinkedList<HashMap<String, Object>> infoList = getRow("kom_activity_open_cdkey", where);
				if ( infoList.size() <= 0 ) {
					LinkedHashMap<String, Object> data = new LinkedHashMap<String, Object>();
					data.put("CdKey", cdKey);
					data.put("Type", type);
					data.put("AddTime", nowTime);
					data.put("UpdateTime", nowTime);
					
					insert("kom_activity_open_cdkey", data);
					
					sqls = sqls.append("INSERT INTO `kom_activity_open_cdkey` (`CdKey`, `ToolList`, `Type`, `IsUse`, `UserId`, `UserRoleId`, `AddTime`, `UpdateTime`) VALUES"
									  +"('"+cdKey+"', NULL, "+type+", 0, 0, 0, "+nowTime+", "+nowTime+")");
					sqls.append(";");
					sqls.append("\n");
					break;
				}else{
					System.out.println("重复");
					continue;
				}
			}
		}
		
		writeToFile(sqls,filePath);
	}
	
	public static String generateKey(int rndCharLength){
		String charList = "abcdefghijklmnopqrstuvwxyz0123456789";
		StringBuilder strBuilder = new StringBuilder();
		while ( true ) {
			int charLength = charList.length();
			Random random = new Random();
			if ( strBuilder.length() >= rndCharLength ) {
				break;
			}
			int index = random.nextInt(charLength);
			strBuilder.append(charList.charAt(index));
		}
		return strBuilder.toString();
	}
	
	public static void generateOldUserCdKey(){
		String keys_filePath = "D:\\MyData\\desk\\keys.txt";
		String email_filePath = "D:\\MyData\\desk\\emails.txt";
		
		LinkedList<HashMap<String, Object>>  data = DbUtil.select("kom_user_role_imp", new LinkedHashMap<String, Object>());
		HashMap<String,Integer> currentKeys = new HashMap<String, Integer>();
		for(int i=0;i<data.size();i++){
			String key = null;
			while(true){
				key=generateKey(6);
				if(currentKeys.containsKey(key)){
					continue;
				}else{
					currentKeys.put(key, 1);
					break;
				}
			}
			
			HashMap<String, Object> role = data.get(i);
			int userId = Integer.parseInt(role.get("UserId").toString());
			int roleId = Integer.parseInt(role.get("RoleId").toString());
			
			StringBuilder sb_mails = new StringBuilder();
			StringBuilder sb_keys = new StringBuilder();
			
			System.out.println("生成第:"+i);
			
			String toolListString = null;
//			ArrayList<HashMap<String,Object>> toolList = new ArrayList<HashMap<String,Object>>();
//			HashMap<String,Object> gold = new HashMap<String, Object>();
//			gold.put("toolIndexName", "1jiyushibaoxiang4");
//			gold.put("count", 2);
//			toolList.add(gold);
//			toolListString = JsonUtil.encodeJson(toolList);
			 
			String title = "《将爆三国》畅玩专区停服邮件";
			String content = "亲爱的《将爆三国》用户：\\n"+
							 "由于运营合作协议到期,我们十分抱歉地通知大家《将爆三国》畅玩专区预定于2013年8月9日上午10:00正式停止各项运营工作,关闭服务器。\\n"+
							 "秉持对大家负责的态度,同时也为了回馈广大玩家的深情厚谊,我们将赠送一份激活码,此激活码可用于2服[ff2222]【国色天香】[-]兑换老玩家回馈大礼,让您的欢乐在这里得到延续！\\n"+
							 "激活码：[ff2222]"+key+"[-]（请妥善保存）\\n"+
							 "奖励内容：[ff2222]等同原充值额100%的元宝[-],原等级*5000的银两,原等级*1000的将魂。";
			sb_mails.append(generateEmail(userId,roleId,title,content,toolListString));
			sb_mails.append("\n");
			writeToFile(sb_mails,email_filePath);
			
			sb_keys.append(role.get("RoleName"));
			sb_keys.append("     "+key);
			sb_keys.append("\n");
			writeToFile(sb_keys,keys_filePath);
		}
	}
	
	
	public static void sendTotalUserEmail(int sendGold){
		String filePath = "D:\\MyData\\desk\\emails.txt";
		String sql = "select `RoleId`,`UserId`,`RoleName` from `kom_user_role_imp3` ";
		
		 LinkedList<HashMap<String, Object>>  result = DbUtil.getInstance().query(sql);
		 for(int i=0;i<result.size();i++){
			 StringBuilder sb = new StringBuilder();
			 System.out.println("生成第:"+i);
			 
//			 String keysql = "select `CdKey` from `kom_activity_open_cdkey` where `Id` = "+(1+i);
//			 LinkedList<HashMap<String, Object>>  keyResult = DbUtil.getInstance().query(keysql);
//			 String key = keyResult.get(0).get("CdKey").toString();
			 
			 HashMap<String, Object> child = result.get(i);
			 int iUserId = Integer.parseInt(child.get("UserId").toString());
			 int iRoleId = Integer.parseInt(child.get("RoleId").toString());
			 
//			 String accsql = "select `UserAccount` from `kom_user_account_imp` where `UserId` = "+iUserId;
//			 LinkedList<HashMap<String, Object>>  accResult = DbUtil.getInstance().query(accsql);
//			 String account = accResult.get(0).get("UserAccount").toString();
			 
			 String toolListString = null;
			 ArrayList<HashMap<String,Object>> toolList = new ArrayList<HashMap<String,Object>>();
			 HashMap<String,Object> gold = new HashMap<String, Object>();
			 gold.put("toolIndexName", "1jiyushibaoxiang4");
			 gold.put("count", 2);
			 toolList.add(gold);
			 toolListString = JsonUtil.encodeJson(toolList);
			 
			 String title = "《将爆三国》补偿邮件";
			 String content = "尊敬的各位将领：\\n   《将爆三国》服务器在每周二9点～10点之间，都会进行例行停服维护。提升服务器稳定性。在维护后会有一份特殊奖励送上哟，感谢您对《将爆三国》的支持与厚爱！";
			 
			 sb.append(generateEmail(iUserId,iRoleId,title,content,toolListString));
			 sb.append("\n");
			 writeToFile(sb,filePath);
		 }
		
	}
	
	public static String generateEmail(int userId ,int userRoleId,String title,String content,String toolList){
		int time = DateUtil.getNowTime();
		String result = "INSERT INTO `kom_email_info` (" +
				"`SenderUserId`, `SenderUserRoleId`, `ReceiveUserId`, `ReceiveUserRoleId`, `EmailType`, `EmailTitle`, `EmailContent`, `IsOpen`, `OpenTime`, `IsDelete`, `IsSendDelete`, `DeleteTime`, `AddTime`, `ToolList`, `IsReceiveToolList`)" +
				" VALUES(" +
				"0,0,"+userId+","+userRoleId+",1," +
				"'"+title+"', " +
				"'"+content+"', 0, NULL, 0, 0, NULL, "+time+","+(toolList!=null?"'"+toolList+"'":"NULL")+", 0);";
		return result;
	}
	
	
	public static void writeToFile(StringBuilder sb,String filePath)
    {
        byte[] buff=new byte[]{};
        try
        {
            buff=sb.toString().getBytes();
            FileOutputStream out=new FileOutputStream(filePath,true);
            out.write(buff,0,buff.length);
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
	
}
